{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import MetaData, Table, create_engine\n",
    "metadata = MetaData()\n",
    "engine = create_engine('sqlite:///Chinook_Sqlite.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "artist = Table('artist', metadata, autoload=True, autoload_with=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "album = Table('album', metadata, autoload=True, autoload_with=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ArtistId', 'Name']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist.columns.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'AC/DC'),\n",
       " (2, 'Accept'),\n",
       " (3, 'Aerosmith'),\n",
       " (4, 'Alanis Morissette'),\n",
       " (5, 'Alice In Chains'),\n",
       " (6, 'Antônio Carlos Jobim'),\n",
       " (7, 'Apocalyptica'),\n",
       " (8, 'Audioslave'),\n",
       " (9, 'BackBeat'),\n",
       " (10, 'Billy Cobham')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import select\n",
    "s = select([artist]).limit(10)\n",
    "engine.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "album.foreign_keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import ForeignKeyConstraint\n",
    "album.append_constraint(\n",
    "    ForeignKeyConstraint(['ArtistId'], ['artist.ArtistId'])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('album', MetaData(bind=None), Column('AlbumId', INTEGER(), table=<album>, primary_key=True, nullable=False), Column('Title', NVARCHAR(length=160), table=<album>, nullable=False), Column('ArtistId', INTEGER(), ForeignKey('artist.ArtistId'), table=<album>, nullable=False), schema=None)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadata.tables['album']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'artist JOIN album ON artist.\"ArtistId\" = album.\"ArtistId\"'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(artist.join(album))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "metadata.reflect(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['track', 'InvoiceLine', 'Employee', 'Invoice', 'album', 'Genre', 'PlaylistTrack', 'Album', 'Customer', 'MediaType', 'Artist', 'Track', 'artist', 'Playlist'])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadata.tables.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "playlist = metadata.tables['Playlist']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Music'),\n",
       " (2, 'Movies'),\n",
       " (3, 'TV Shows'),\n",
       " (4, 'Audiobooks'),\n",
       " (5, '90’s Music'),\n",
       " (6, 'Audiobooks'),\n",
       " (7, 'Movies'),\n",
       " (8, 'Music'),\n",
       " (9, 'Music Videos'),\n",
       " (10, 'TV Shows')]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import select\n",
    "s = select([playlist]).limit(10)\n",
    "engine.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
